# 导入模块
import requests
import pymysql
import time

# 设置时间格式
current_time = int(time.time() * 1000)
# 链接mysql数据库
con = pymysql.connect(
            host='localhost',
            user='root',
            password='123456',
            port=3306,
            database='db_dfcf'
        )
# 创建游标对象
cursor = con.cursor()

# 删除表
sql = 'drop table tb_dfcf'
print(sql)
cursor.execute(sql)
# 新建表
sql = 'create table tb_dfcf(id int primary key auto_increment not null,dm varchar(50),gpm varchar(50),znew varchar(50),zmin varchar(50));'
print(sql)
cursor.execute(sql)
# 网址
for i in range(1, 60):
    print('开始读取第', i, '页')
    url = f'https://43.push2.eastmoney.com/api/qt/clist/get?pn={i}&pz=20&po=1&np=1&ut=bd1d9ddb04089700cf9c27f6f7426281&fltt=2&invt=2&dect=1&wbp2u=|0|0|0|web&fid=f3&fs=m:0+t:6,m:0+t:80,m:1+t:2,m:1+t:23,m:0+t:81+s:2048&fields=f1,f2,f3,f4,f5,f6,f7,f8,f9,f10,f12,f13,f14,f15,f16,f17,f18,f20,f21,f23,f24,f25,f22,f11,f62,f128,f136,f115,f152&_={current_time}'
    # 链接网址
    response = requests.get(url)
    # 获取数据
    datas = response.json()["data"]["diff"]
    for data in datas:
        # l = []
        print(f"当前读取的是{data['f14']}")
        # d = {
        #     "代码": data['f12'],
        #     "股票名": data['f14'],
        #     "最新价": data['f3'],
        #     "最低价": data['f17']
        # }
        # l.append(d)
        # str0 = str(l)
        time.sleep(0.1)

        # 添加数据到mysql
        sql = f"insert into tb_dfcf(dm,gpm,znew,zmin) values('{data['f12']}','{data['f14']}','{data['f3']}','{data['f17']}')"
        print(sql)
        cursor.execute(sql)
    print('第', i, '页读取成功')

# 提交事务 手动提交
con.commit()

# 释放资源
cursor.close()
con.close()
